/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

// create database
var Plan_Arr = [
    [ 1,"CIS" ,"Computer Information System"],
    [ 2,"BIT" ,"Business Information Technology"]
];
var Group_Arr = [
    [1 ,"University Mandatory"],//0
    [2 ,"University Optional"],//1
    [3 ,"Faculty Mandatory CIS"],//2
    [4 ,"Mandatory-CIS"],//3
    [5,"Mandatory-BIT"],//4
    [6 ,"Optional-CIS"],//5
    [7 ,"Optional-BIT"],//6
    [8 ,"Faculty Mandatory BIT"]//7
];


var Course_Arr = [
    ["5401101","Calculus 1 - (التفاضل والتكامل1 )" ,"",1],//0
    ["5401131","Statistics - (مبادئ الاحصاء)" ,"",1],//1
    ["5401201","C++ - (المهارات الحاسوبية للكليات العلمية)" ,"",2],// 2
    ["5401204","Digital Logic - (منطق رقمي )" ,"",2],//3
    ["5401231","Data Structures - (تراكيب البيانات )" ,"5402211" ,2],//4
    ["5402101","Fundemental information technology - (أساسيات تكنولوجيا المعلومات)" ,"" ,1],//5
    ["5402121","Web 1 - (تطوير تطبيقات الويب1)" ,"5402101" ,1],//6
    ["5402232","Management Information System - (أنظمة المعلومات الادارية )" ,"5402101" ,2],//7   
    // speacialty mandatory 
   ["5401102","Calculus 2 - (التفاضل والتكامل -2)" ,"5401101" ,1],//8
   ["5401105","Discrete  - (رياضيات منفصلة )" ,"" ,2],//9
   ["5402201","Interpersonal Communication - (التوثيق والتواصل)" ,"5402101" ,2],//10
   ["5401341","Algorithms  - ( نظرية الخوارزميات )" ,"5401231" ,3],//11
   ["5401359","Computer Graphics - (الرسم بالحاسوب )" ,"5401231" ,3],//12,
   ["5401361","Network 1 - (شبكات الحاسوب 1)" ,"5401231" ,3],//13
   ["5401473","Operating Systems - (نظم التشغيل )" ,"5401231" ,4],//14
   ["5402211","Java 1 - (برمجة الكائنات الموجهة 1)" ,"5401201" ,2],//15
   ["5402321","Database - (نظم قواعد البيانات)" ,"5401231" ,3],//16
   ["5401342","Artificial Intelligence - (الذكاء الصطناعي)" ,"5401231" ,3],//17
   ["5402351","Multimedia - (الوسائط المتعددة)" ,"5402211" ,3],//18
   ["5401353","Human Computer Interaction - (تفاعل الانسان مع الحاسوب )" ,"5401359" ,3],//19
   ["5402353","Web 2 - (تطوير تطبيقات الويب2)" ,"5402121" ,3],//20
   ["5402451","Geographical Information system - (أنظمة المعلومات الجغرافية ) )" ,"5401359" ,4],//21
   ["5402471","Software Engineering - ( هندسة البرمجيات)" ,"5402321" ,4],//22
   ["5402453","Information systems applications - ( تطبيقات أنظمة المعلومات)" ,"5402321" ,4],//23
   ["5401215","Advanced Programming - (برمجة متقدمة)" ,"5401201" ,2],//24
   ["5402322","Security - (سرية وأمن المعلومات)" ,"5401231" ,3],//25
   ["5402475","System analysis - (تحليل نظم )" ,"5402321" ,4],//26
   ["5402352","Web Publishing - (النشر على الانترنت)" ,"5402121" ,3],//27
   ["5402498","Trainning - (التدريب )" ,"" ,4],//28
   ["5402495","Graduation Project - ( مشروع التخرج)" ,"" ,4],//29
    // optional
   ["5401241","Computation theory - (نظرية الحسابات )","5401105" ,2],//30
   ["5402212","Java 2 - (برمجة الكائنات الموجهة 2)","5402211" ,2],//31
   ["5401356","Pattern recognition - (تمييز الانماط)","5401231" ,3],//32
   ["5401203","Visual programming - (برمجة مرئية)","5401201" ,2],//33
   ["5401355","Computer-aided learning - (التعلم بمساعدة الحاسوب )","5402212" ,3],//34
   ["5401425","Distributed Database - (قواعد البيانات الموزعة )","5402321" ,4],//35
   ["5401442","Machine Learning - ( التعليم الآلي)","5401342" ,4],//36
   ["5401445","Natural Language Processing - ( معالجة اللغات الطبيعية)","5401342" ,4],//37
   ["5402354","E_Business - ( الأعمال الالكترونية)","5402352" ,3],//38
   ["5402252","Electronic Systems Applications - (تطبيقات الأنظمة الالكترونية )","5402232" ,2],//39
   ["5401455","Image processing - (معالجة الصور )","5401359" ,4],//40
   ["5402415","Oracle - (لغات قواعد البيانات وأدواتها)","5402321" ,4],//41
   ["5402345","Computer Ethics - (أخلاقيات الحاسوب )","5402101" ,3],//42
   ["5402481","Quality Management - (إدارة الجودة )","5401131" ,3],//43
   ["5401456","Expert systems and neural networks - (النظم الخبيرة والشبكات العصبية)","5401342" ,4],//44
   ["5402332","Decision support systems - (أنظمة دعم القرار )","5402321" ,3],//45
   ["5402458","Software certified - (برمجيات معتمدة)","" ,4],//46
   ["5401477","Design-oriented objects - (تصميم الكائنات الموجهة )","5402471" ,4],//47
   ["5402494","Special Topics - (موضوعات خاصة)","" ,4],//48

   // BIT 
   // speacilaty mandtory 
    ["5202101","Accounting Principles 1 -(مبادئ المحاسبة)","" ,1],//49
    ["5202211","Accounting management - (محاسبة أدارية)","5202101" ,2],//50
    ["5203211","Principles of Financial Management - (مبادئ الأدارة المالية)","5202101" ,2],//51
    ["5201101","Principles of Business Administration - (مبادئ أدارة الأعمال)","" ,2],//52
    ["5402234","Operational Research - (بحوث العمليات )" ,"5401201" ,2],//53
    ["5402357","E-government - (الحكومة الإلكترونية)" ,"5402232" ,3],//54
   
   // optional 
   ["5201408"," LeaderShip in business - (الريادية في الأعمال )" ,"5402232" ,4],//55
   ["5202333","Accounting Information Systems - (نظم معلومات محاسبية )" ,"5202101" ,3],//56
   ["5203311","Financial Analysis - (التحليل المالي )" ,"5203211" ,3],//57
   ["5203444","Risk Management - (ادارة المخاطر )" ,"5203211" ,4],//58
   ["5402235","Management Of Information Source  - (ادارةمصادر المعلومات )" ,"5402101" ,2],//59
   ["5402356","Stastistcal Packages  - (الحزم الاحصائية)" ,"5401131" ,3],//60
   ["5402483","Business Requiermants Analysis - (تحليل متطلبات الاعمال )","5402475" ,4],//61
   ["5402472","Business Intellegence Systems - (أنظمة الاعمال الذكية )","5402232" ,4]//62

];//end of courses Array
var GC_Arr= [
    [Group_Arr[2][0],Course_Arr[0][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[1][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[2][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[3][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[4][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[5][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[6][0],Plan_Arr[0][0]],
    [Group_Arr[2][0],Course_Arr[7][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[8][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[9][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[10][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[11][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[12][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[13][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[14][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[15][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[16][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[17][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[18][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[19][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[20][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[21][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[22][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[23][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[24][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[25][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[26][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[27][0],Plan_Arr[0][0]],
    [Group_Arr[3][0],Course_Arr[29][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[30][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[31][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[32][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[33][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[34][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[35][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[36][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[37][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[38][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[39][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[40][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[41][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[42][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[43][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[44][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[45][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[46][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[47][0],Plan_Arr[0][0]],
    [Group_Arr[5][0],Course_Arr[48][0],Plan_Arr[0][0]],
    [Group_Arr[7][0],Course_Arr[0][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[1][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[2][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[3][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[4][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[5][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[6][0],Plan_Arr[1][0]],
    [Group_Arr[7][0],Course_Arr[7][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[49][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[50][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[51][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[52][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[11][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[13][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[14][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[15][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[16][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[22][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[26][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[39][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[53][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[27][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[38][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[43][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[10][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[25][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[54][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[18][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[29][0],Plan_Arr[1][0]],
    [Group_Arr[4][0],Course_Arr[28][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[55][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[56][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[57][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[58][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[59][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[45][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[42][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[20][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[60][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[41][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[46][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[61][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[62][0],Plan_Arr[1][0]],
    [Group_Arr[6][0],Course_Arr[48][0],Plan_Arr[1][0]]   
];// end Group_Course array
// create class table
   //query.executeSql('CREATE TABLE IF NOT EXISTS Class (Class_num , Course_num , Section_num , Day , Time , Class_room , Teacher , Section_status  )');
  // query.executeSql('CREATE TABLE IF NOT EXISTS Course_taken (ID INTEGER PRIMARY KEY, Course_num VARCHAR(8) , year INTEGER , semester INTEGER, FOREIGN KEY (Course_num) REFERENCES Course(Course_num) )');
        
var db = openDatabase('Guiding App DB', '1.0', 'database web sql', 20 * 1024 * 1024);
db.transaction(function (query) { 
query.executeSql('CREATE TABLE IF NOT EXISTS Plan(Plan_id INTEGER ,Abbreviation VARCHAR(3), Full_name VARCHAR(10))');
for ( var i = 0; i < Plan_Arr.length ; i++ )
{
    var column_num = 0;
    query.executeSql( 'INSERT INTO Plan VALUES ('+Plan_Arr[i][column_num]+','+Plan_Arr[i][column_num+1] +','+Plan_Arr[i][column_num+1]+')');
}//end for loop
// Group table
});
db.transaction(function (query) { 
 query.executeSql('CREATE TABLE IF NOT EXISTS Grouping(Group_num INTEGER , Group_name  VARCHAR(20),  PRIMARY KEY(Group_num))');
 for ( var j = 0; j < Group_Arr.length ; j++ )
{
    var column_num = 0;
    query.executeSql( 'INSERT INTO Plan VALUES ('+Group_Arr[j][column_num]+','+Group_Arr[j][column_num+1] +')');
}//end for loop
query.executeSql('CREATE TABLE IF NOT EXISTS Course(Course_num VARCHAR(8), Course_name VARCHAR(40) ,Prev_course VARCHAR(8),course_level INTEGER ,PRIMARY KEY(Course_num) )');
for ( var x = 0; x < Group_Arr.length ; x++ )
{
    var column_num = 0 ;
    query.executeSql( 'INSERT INTO Course VALUES ('+Course_Arr[x][column_num]+','+ Course_Arr[x][column_num+1]+',' +Course_Arr[x][column_num+1]+')');
}//end for loop
//creat Group_Course table
 query.executeSql('CREATE TABLE IF NOT EXISTS Group_Course (Group_num INTEGER, Course_num VARCHAR(8),Plan_id VARCHAR(8) ,FOREIGN KEY (Course_num) REFERENCES Course(Course_num),FOREIGN KEY (Plan_id) REFERENCES Plan(Plan_id) ,PRIMARY KEY (Group_num , Course_num) )');
for ( var y = 0; y < GC_Arr.length ; y++ )
{
    var column_num = 0 ;
    query.executeSql( 'INSERT INTO  Group_Course VALUES ('+GC_Arr[y][column_num]+','+GC_Arr[y][column_num+1]+','+GC_Arr[y][column_num+1]+')');
}
    
    // create student table 
query.executeSql('CREATE TABLE IF NOT EXISTS Student(  Student_id VARCHAR(10),Student_name VARCHAR(40) ,Student_pass VARCHAR(30) , Student_Department VARCHAR(8) ,year_subscription VARCHAR(8) , PRIMARY KEY(Student_id))');
// create class table 
query.executeSql('CREATE TABLE IF NOT EXISTS Class (Class_num , Course_num , Section_num , Day , Time , Class_room , Teacher , Section_status  )');
// create course table 
query.executeSql('CREATE TABLE IF NOT EXISTS Course_taken (ID INTEGER PRIMARY KEY, Course_num VARCHAR(8) , year INTEGER , semester INTEGER, FOREIGN KEY (Course_num) REFERENCES Course(Course_num) )');
 });// end function
 